package com.ruoyi.report.forms.util;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ruoyi.common.core.text.Convert;
import com.ruoyi.report.forms.domain.ReportFormsTemplate;
import org.apache.commons.io.FilenameUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.awt.Color;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

public class ExcelHelper {

    // 弃用 2024-3-12
    public static void ExportExcel(HttpServletResponse response, ReportFormsTemplate template) {
        try (SXSSFWorkbook workbook = new SXSSFWorkbook(500)) {
            SXSSFSheet sheet = workbook.createSheet();
            workbook.setSheetName(0, template.getTemplateName());

            JSONObject json = JSON.parseObject(template.getTemplateConfig());
            JSONObject range = json.getJSONObject("range");
            JSONArray width = json.getJSONArray("width");
            JSONArray height = json.getJSONArray("height");

            int rowNumber = range.getIntValue("row");
            int colNumber = range.getIntValue("col");

            // 初始化所有单元格
            for (int i = 0; i <= rowNumber; i++) {
                SXSSFRow row = sheet.createRow(i);
                row.setHeightInPoints(height.getFloatValue(i));
                for (int j = 0; j <= colNumber; j++) {
                    row.createCell(j);
                }
            }

            // 行高
            for (int i = 0; i <= rowNumber; i++) {
                sheet.getRow(i).setHeightInPoints(height.getFloatValue(i));
            }

            // 列宽
            for (int i = 0; i <= colNumber; i++) {
                sheet.setColumnWidth(i, (int) (width.getFloatValue(i) * 256));
            }

            // 添加单元格内容
            JSONArray cells = json.getJSONArray("cells");
            for (int i = 0; i < cells.size(); i++) {
                JSONObject info = cells.getJSONObject(i);
                int rowIndex = info.getIntValue("row");
                int colIndex = info.getIntValue("col");
                int rowSpan = info.getIntValue("rowspan");
                int colSpan = info.getIntValue("colspan");
                String text = info.getString("text");
                if (rowSpan != 0 || colSpan != 0) {
                    sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + rowSpan, colIndex, colIndex + colSpan));
                }
                SXSSFCell cell = sheet.getRow(rowIndex).getCell(colIndex);
                cell.setCellValue(text);

                XSSFFont font = (XSSFFont) workbook.createFont();
                XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();

                // 文本格式
                String textAlign = info.getString("textAlign");
                if (textAlign != null) {
                    switch (textAlign) {
                        case "left":
                            style.setAlignment(HorizontalAlignment.LEFT);
                            break;
                        case "center":
                            style.setAlignment(HorizontalAlignment.CENTER);
                            break;
                        case "right":
                            style.setAlignment(HorizontalAlignment.RIGHT);
                            break;
                    }
                }
                style.setVerticalAlignment(VerticalAlignment.CENTER);

                // 粗体
                String bold = info.getString("bold");
                if (bold != null) {
                    font.setBold(true);
                }

                // 斜体
                String italic = info.getString("italic");
                if (italic != null) {
                    font.setItalic(true);
                }

                // 下划线
                String underline = info.getString("underline");
                if (underline != null) {
                    font.setUnderline((byte) 1);
                }

                // 字号
                String fontSize = info.getString("fontSize");
                if (fontSize != null) {
                    font.setFontHeightInPoints(Convert.toShort(fontSize));
                } else {
                    font.setFontHeightInPoints((short) 11);
                }

                // 文本颜色
                String color = info.getString("color");
                if (color != null) {
                    font.setColor(new XSSFColor(new Color(Integer.decode("0x" + color.replace("#", ""))), new DefaultIndexedColorMap()));
                }

                // 填充颜色
                String background = info.getString("background");
                if (background != null) {
                    style.setFillForegroundColor(new XSSFColor(new Color(Integer.decode("0x" + background.replace("#", ""))), new DefaultIndexedColorMap()));
                    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                }

                style.setFont(font);
                cell.setCellStyle(style);
            }

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    // 弃用 2024-3-12
    public static HashMap<String, Object> ImportExcel(MultipartFile file) {
        String ext = Objects.requireNonNull(FilenameUtils.getExtension(file.getOriginalFilename())).toLowerCase();
        try (InputStream is = file.getInputStream()) {
            Workbook workbook;
            FormulaEvaluator formulaEvaluator;
            if (ext.equals("xlsx")) {
                workbook = new XSSFWorkbook(is);
                formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
            /*} else if (ext.equals("xls")) {
                workbook = new HSSFWorkbook(is);
                formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);*/
            } else {
                throw new RuntimeException(new Exception("文件类型不对"));
            }

            Sheet sheet = workbook.getSheetAt(0);

            HashMap<String, Integer> range = new HashMap<>();

            int row = sheet.getLastRowNum();
            int col = 0;
            for (int i = 0; i < row; i++) {
                int c = sheet.getRow(i).getLastCellNum() - 1;
                if (col < c) {
                    col = c;
                }
            }

            if (row >= 60 || col >= 40) {
                throw new RuntimeException(new Exception("Excel超出有效大小"));
            }

            range.put("row", row);
            range.put("col", col);

            float[] width = new float[col + 1];
            float[] height = new float[row + 1];
            for (int i = 0; i <= row; i++) {
                height[i] = sheet.getRow(i).getHeightInPoints();
            }
            for (int i = 0; i <= col; i++) {
                width[i] = (float) (sheet.getColumnWidth(i)) / 256f;
            }

            List<CellRangeAddress> regions = sheet.getMergedRegions();

            ArrayList<HashMap<String, Object>> cells = new ArrayList<>();
            for (int i = 0; i <= row; i++) {
                for (int j = 0; j <= col; j++) {
                    Cell cell = sheet.getRow(i).getCell(j);
                    if (cell == null) {
                        continue;
                    }
                    XSSFCellStyle style = (XSSFCellStyle) cell.getCellStyle();
                    XSSFFont font = style.getFont();

                    CellRangeAddress region = null;
                    for (CellRangeAddress cellAddresses : regions) {
                        if (cellAddresses.getFirstRow() == i && cellAddresses.getFirstColumn() == j) {
                            region = cellAddresses;
                            break;
                        }
                    }

                    HashMap<String, Object> info = new HashMap<>();
                    info.put("row", i);
                    info.put("col", j);
                    if (region != null) {
                        info.put("rowspan", region.getLastRow() - region.getFirstRow());
                        info.put("colspan", region.getLastColumn() - region.getFirstColumn());
                    }

                    switch (cell.getCellType()) {
                        case NUMERIC:
                            info.put("text", cell.getNumericCellValue());
                            break;
                        case FORMULA:
                            CellValue value = formulaEvaluator.evaluate(cell);
                            switch (cell.getCachedFormulaResultType()) {
                                case NUMERIC:
                                    info.put("text", value.getNumberValue());
                                    break;
                                case STRING:
                                    info.put("text", value.getStringValue());
                                    break;
                            }
                            break;
                        case STRING:
                        case BLANK:
                            info.put("text", cell.getStringCellValue());
                            break;
                    }

                    if (style.getAlignment() == HorizontalAlignment.CENTER) {
                        info.put("textAlign", "center");
                    } else if (style.getAlignment() == HorizontalAlignment.RIGHT) {
                        info.put("textAlign", "right");
                    } else {
                        info.put("textAlign", "left");
                    }

                    if (font.getBold()) {
                        info.put("bold", "600");
                    }

                    if (font.getItalic()) {
                        info.put("italic", "italic");
                    }

                    if (font.getUnderline() == 1) {
                        info.put("underline", "underline");
                    }

                    if (font.getFontHeightInPoints() != 11) {
                        info.put("fontSize", font.getFontHeightInPoints());
                    }

                    XSSFColor color = font.getXSSFColor();
                    if (color != null) {
                        info.put("color", "#" + color.getARGBHex().substring(2));
                    }

                    XSSFColor background = style.getFillForegroundXSSFColor();
                    if (background != null) {
                        info.put("background", "#" + background.getARGBHex().substring(2));
                    }

                    cells.add(info);
                }
            }

            HashMap<String, Object> json = new HashMap<>();
            json.put("range", range);
            json.put("width", width);
            json.put("height", height);
            json.put("cells", cells);

            return json;
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    // 导出
    public static void ExportSpreadsheet(HttpServletResponse response, ReportFormsTemplate template, String date, String month) {
        try (SXSSFWorkbook workbook = new SXSSFWorkbook(500)) {
            SXSSFSheet sheet = workbook.createSheet();
            workbook.setSheetName(0, template.getTemplateName());

            // 合并单元格
            JSONObject config = JSON.parseObject(template.getTemplateConfig());
            JSONArray merges = config.getJSONArray("merges");
            for (Object m : merges) {
                // 解析要合并的单元格范围并且合并
                String merge = (String) m;
                String[] cells = merge.split(":");
                if (cells.length < 2) {
                    continue;
                }
                CellReference startCellRef = new CellReference(cells[0]);
                CellReference endCellRef = new CellReference(cells[1]);
                CellRangeAddress cellRangeAdd = new CellRangeAddress(startCellRef.getRow(), endCellRef.getRow(), startCellRef.getCol(), endCellRef.getCol());
                sheet.addMergedRegion(cellRangeAdd);
            }

            // 获取后台数据
            JSONArray bindata = config.getJSONArray("bindata");
            ExcelDataSelect excelDataSelect = new ExcelDataSelect(sheet, bindata, date, month);

            // 创建excel
            JSONObject rows = config.getJSONObject("rows");
            ArrayList<Integer> rowsKey = ExcelHelper.ConvertStringArrayToIntArray(rows.keySet().toArray());

            // 获取样式列表
            JSONArray styles = config.getJSONArray("styles");

            // 添加单元格以及数据
            for (int ri : rowsKey) {
                JSONObject row = rows.getJSONObject(String.valueOf(ri));
                JSONObject cols = row.getJSONObject("cells");
                ArrayList<Integer> colsKey = ExcelHelper.ConvertStringArrayToIntArray(cols.keySet().toArray());
                for (int ci : colsKey) {
                    JSONObject col = cols.getJSONObject(String.valueOf(ci));
                    SXSSFCell cell = ExcelHelper.GetCell(sheet, ri, ci);
                    String text = col.getString("text");

                    // 判断单元格数据类型
                    if (NumberUtils.isCreatable(text)) {
                        cell.setCellValue(Double.parseDouble(text));
                    } else {
                        if (!StringUtils.isEmpty(text)) {
                            // 判断是否存在公式，以单元格内容的第一个字符为判断条件
                            if (text.charAt(0) == '=') {
                                cell.setCellFormula(text);
                            } else {
                                cell.setCellValue(text);
                            }
                        }
                    }

                    // 给表格添加样式，至于为什么这么复杂，那是用语言说不清的
                    String isStyle = col.getString("isStyle");
                    if (isStyle != null) {
                        continue;
                    }
                    JSONArray merge = col.getJSONArray("merge");
                    if (merge == null) {
                        ExcelHelper.SetCellStyle(workbook, cell, col, styles);
                        continue;
                    }
                    int mergeRow = merge.getInteger(0);
                    int mergeCol = merge.getInteger(1);
                    for (int m = 0; m <= mergeRow; m++) {
                        for (int n = 0; n <= mergeCol; n++) {
                            ExcelHelper.SetCellStyle(workbook, ExcelHelper.GetCell(sheet, m + ri, n + ci), col, styles);
                            JSONObject tmp = rows.getJSONObject(String.valueOf(m + ri));
                            if (tmp == null) {
                                continue;
                            }
                            tmp = tmp.getJSONObject("cells");
                            if (tmp == null) {
                                continue;
                            }
                            tmp = tmp.getJSONObject(String.valueOf(n + ci));
                            if (tmp == null) {
                                continue;
                            }
                            tmp.put("isStyle", "0");
                        }
                    }
                }
            }

            // 将数据写到Excel上
            excelDataSelect.WriteExcelData();

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    // 给模板中的单元格行列按照数字类型进行排序
    private static ArrayList<Integer> ConvertStringArrayToIntArray(Object[] objArray) {
        String[] strArray = Arrays.copyOf(objArray, objArray.length, String[].class);
        ArrayList<Integer> intList = new ArrayList<>();
        for (String s : strArray) {
            if (!NumberUtils.isCreatable(s)) {
                continue;
            }
            intList.add(Integer.parseInt(s));
        }
        Collections.sort(intList);
        return intList;
    }

    // 获取单元格，如果获取不到，则递归创建
    private static SXSSFCell GetCell(SXSSFSheet sheet, int ri, int ci) {
        SXSSFRow row = sheet.getRow(ri);
        if (row == null) {
            sheet.createRow(ri);
            row = sheet.getRow(ri);
        }
        SXSSFCell cell = row.getCell(ci);
        if (cell == null) {
            row.createCell(ci);
            cell = row.getCell(ci);
        }
        return cell;
    }

    // 设置单元格样式
    private static void SetCellStyle(SXSSFWorkbook workbook, SXSSFCell cell, JSONObject col, JSONArray styles) {
        String index = col.getString("style");
        if (StringUtils.isEmpty(index)) {
            return;
        }

        JSONObject style = styles.getJSONObject(Integer.parseInt(index));
        if (Objects.isNull(style)) {
            return;
        }

        XSSFFont font = (XSSFFont) workbook.createFont();
        XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
        cellStyle.setWrapText(true);

        for (String item : style.keySet()) {
            switch (item) {
                case "border": // 边框
                    JSONObject border = style.getJSONObject(item);
                    JSONArray top = border.getJSONArray("top");
                    if (!Objects.isNull(top)) {
                        cellStyle.setBorderTop(BorderStyle.THIN);
                    }
                    JSONArray left = border.getJSONArray("left");
                    if (!Objects.isNull(left)) {
                        cellStyle.setBorderLeft(BorderStyle.THIN);
                    }
                    JSONArray right = border.getJSONArray("right");
                    if (!Objects.isNull(right)) {
                        cellStyle.setBorderRight(BorderStyle.THIN);
                    }
                    JSONArray bottom = border.getJSONArray("bottom");
                    if (!Objects.isNull(bottom)) {
                        cellStyle.setBorderBottom(BorderStyle.THIN);
                    }
                    break;
                case "valign": // 垂直居中
                    String valign = style.getString(item);
                    switch (valign) {
                        case "top":
                            cellStyle.setVerticalAlignment(VerticalAlignment.TOP);
                            break;
                        case "middle":
                            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                            break;
                        case "bottom":
                            cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
                            break;
                    }
                    break;
                case "align": // 水平居中
                    String align = style.getString(item);
                    switch (align) {
                        case "left":
                            cellStyle.setAlignment(HorizontalAlignment.LEFT);
                            break;
                        case "center":
                            cellStyle.setAlignment(HorizontalAlignment.CENTER);
                            break;
                        case "right":
                            cellStyle.setAlignment(HorizontalAlignment.RIGHT);
                            break;
                    }
                    break;
            }
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
        }
    }
}
